# -*- coding: utf-8 -*-
# Time : 2025/2/13
# Author : 'GJY'
# Software: PyCharm
# @FileName :Fary_text_02.py
import csv
import sqlite3
import pandas as pd
import utils.configFinRAG as configFinRAG


def execute_sql_query(sql_query, cursor):
    """
    执行 SQL 查询并返回执行结果和执行状态
    :param sql_query: 要执行的 SQL 查询语句
    :param cursor: 数据库游标对象
    :return: 执行状态（1 表示成功，0 表示失败）和执行结果
    """
    try:
        cursor.execute(sql_query)
        query_result = cursor.fetchall()
        result_str = str(query_result)
        execution_status = 1
    except Exception as e:
        result_str = f"error: {str(e)}"
        execution_status = 0
        print(f"执行 SQL 查询时出错：{sql_query}，错误信息：{e}")
    return execution_status, result_str


if __name__ == '__main__':
    try:
        # 读取生成的 SQL 文件
        sql_generated_df = pd.read_csv(configFinRAG.question_sql_path, delimiter=",", header=0)

        # 打开用于存储 SQL 执行结果的文件
        with open(configFinRAG.question_sql_check_path, 'w', newline='', encoding='utf-8-sig') as result_file:
            csv_writer = csv.writer(result_file)
            csv_writer.writerow(['问题id', '问题', 'SQL', 'flag', '执行结果'])

            # 连接到数据库
            db_path = r'C:\Users\86138\Desktop\项目结合\Fay-fay-agent-edition0830\agent\tools\data\博金杯比赛数据.db'
            with sqlite3.connect(db_path) as db_connection:
                db_cursor = db_connection.cursor()

                # 遍历生成的 SQL 文件中的每一行
                for _, row in sql_generated_df.iterrows():
                    sql = row['SQL']
                    if sql != 'error':
                        status, result = execute_sql_query(sql, db_cursor)
                        csv_writer.writerow([
                            str(row['问题id']),
                            str(row['问题']),
                            sql,
                            status,
                            result
                        ])
                    else:
                        print(f"跳过 SQL 为 'error' 的问题：{row['问题']}")

    except Exception as e:
        print(f"程序运行过程中出现错误：{e}")